package com.example.g5project.repository.dao;

import com.example.g5project.entity.Consultant;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Optional;

/**
 * @author Hu Zirui
 * @version 1.0.0
 * @ClassName ConsultantDao.java
 * @Description TODO
 * @createTime 2022年03月25日 15:08:00
 */
@Repository
@Transactional
@SuppressWarnings("all")
public interface ConsultantDao extends JpaRepository<Consultant, Integer>, JpaSpecificationExecutor {
    @Query(value = "select * from consultant where name  = ?1", nativeQuery = true)
    Optional<Consultant> findByName(String name);

    @Query(value = "select today_finished_consult from consultant where consultant_id = ?1", nativeQuery = true)
    int getByTodayConsultNumber(int consultant_id);

    @Query(value = "select today_consult_time from consultant where consultant_id = ?1", nativeQuery = true)
    String getByTodayConsultTime(int consultant_id);

    @Query(value = "select * from consultant where is_alive = 1", nativeQuery = true)
    ArrayList<Consultant> getAllAliveConsultant();

    @Query(value = "select sum(today_finished_consult) from consultant", nativeQuery = true)
    int getAllAliveSessionNumber();

    @Modifying
    @Query(value = "delete * from consultant where name = ?1", nativeQuery = true)
    void deleteByName(String name);

    @Modifying
    @Query(value = "update consultant set alive_session_number = alive_session_number + 1 where consultant_id =  ?1", nativeQuery = true)
    void addAliveSessionNumber(Integer id);

    @Modifying
    @Query(value = "update consultant set today_finished_consult = today_finished_consult + 1 where consultant_id =  ?1", nativeQuery = true)
    void addTodaySessionNumber(Integer id);

    @Query(value = "select schedule_date from consultant NATURAL JOIN consultant_schedule where name = ?1 and MONTH(schedule_date) = ?2", nativeQuery = true)
    ArrayList<String> getScheduleByMonth(String name, String month);

    @Query(value = "select avg(score) from chat,consultant where consultant.consultant_id = chat.counselor_id and consultant.name =  ?1", nativeQuery = true)
    Integer getGradeByName(String name);

    @Modifying
    @Query(value = "update consultant set is_free = case when is_free = '1' then '0' when is_free = '0' then '1' end where consultant.name = ?1 ", nativeQuery = true)
    void setStatus(String name);

    @Modifying
    @Query(value = "update consultant set all_finished_consult = all_finished_consult+1 where consultant.name = ?1", nativeQuery = true)
    void increAllConsultNumber(String name);

    @Modifying
    @Query(value = "update consultant set today_finished_consult = today_finished_consult + 1 where name = ?1", nativeQuery = true)
    void incretodayConsultNumber(String name);

    @Modifying
    @Query(value = "update consultant set all_finished_time = ?2 where consultant.name = ?1", nativeQuery = true)
    void updateallHoldTimeByName(String name, String hold_time);

    @Modifying
    @Query(value = "update consultant set today_consult_time = ?2 where consultant.name = ?1", nativeQuery = true)
    void updatetodayHoldTimeByName(String name, String hold_time);

    @Modifying
    @Query(value = "update consultant set alive_session_number = alive_session_number + 1 where name =  ?1", nativeQuery = true)
    void addAliveSessionNumberByName(String name);

    @Modifying
    @Query(value = "update consultant set alive_session_number = alive_session_number - 1 where name =  ?1", nativeQuery = true)
    void minusAliveSessionNumberByName(String name);

    @Modifying
    @Query(value = "update consultant set alive_session_number = alive_session_number - 1 where consultant_id =  ?1", nativeQuery = true)
    void minusAliveSessionNumber(Integer id);

}

